//
// prepSQL.cpp
// libprotea
// Tools Library for the Protea Project / SQL Query Generator for PREP
// Copyright (C) 2003, 2004, 2005 eXegeSys, Inc.
// Copyright (C) 2008 Bruce A. James
//
// This program is part of the Protea Project.
//
// The Protea Project is free software; you can redistribute it and/or modify it 
// under the terms of the GNU General Public License as published by the 
// Free Software Foundation, either version 3 of the License, or (at your 
// option) any later version.
//
// The Protea Project is distributed in the hope that it will be useful, but 
// WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY 
// or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License 
// for more details.
// 
// You should have received a copy of the GNU General Public License along 
// with this program.  If not, see <http://www.gnu.org/licenses/>.
// 
// Please send correspondence to:
// 
// theproteaproject@gmail.com
//

// prepSQL.cpp

#include <iostream>
#include "prepSQL.h"

#include "../defns.h"
using namespace ProteaApp1;

//	Customizer SQL functions
//
//////////////////////////////////////////////////////////////////////////////


char* XCSQLStatement::FixString( XCField* fld ) {
	static char result[ 258 ];

	char* temp = fld->GetOffset();
	while (*temp && *temp == 32)
		temp++;

	bool nonulls = !(fld->GetFlags() & FLAG_ALLOW_NULL);

	if (*temp || nonulls)
	{
		char* dest = result;
			
		if ( nonulls && fld->GetType() == DATE_DB )
			dest += sprintf( dest, "'0001-01-01'" );
		else
			dest += sprintf( dest, "'%s'", temp );

	}
	else
		strcpy( result, "NULL" );

	return result;
};



// XCPrepSQLGen class
//////////////////////

XCPrepSQLGen::XCPrepSQLGen( XCAppInterface* app ) : XCSQLGen( app ) 
{
	dbr = NULL;
	source = NULL;
	target = NULL;
	Clear();
};

XCPrepSQLGen::~XCPrepSQLGen()
{
	Clear();
}

void XCPrepSQLGen::Clear()
{
	// clear out our stuff
	if( dbr )
		delete [] dbr;
	dbr = NULL;
	if( source )
		delete source;
	source = NULL;
	if( target )
		delete target;
	target = NULL;

	dbrCount = 0;
	ConsApp = NULL;
	AutoNum = false;
	XCSQLGen::Clear();	//and everything else
}

void XCPrepSQLGen::MakeCREATE( XCBuffer *buf )
{
	INFUNC( MakeCREATE, app );
	
	type = SQL_STMT_CREATE;
	buffer = buf;

	OUTFUNC();
}

void XCPrepSQLGen::MakeDROP( XCBuffer *buf )
{
	INFUNC( MakeDROP, app );

	type = SQL_STMT_DROP;
	buffer = buf;

	OUTFUNC();
}

void XCPrepSQLGen::MakeADDFIELD( XCBuffer* buf, XCField* fld )
{
	INFUNC( MakeADDFIELD, app );

	Clear();

	type = SQL_STMT_ALTER;
	AlterType = ALTER_ADDFIELD;
	buffer = buf;
	fields = new XCField*[1];
	fields[0] = fld;

	OUTFUNC();
}

void XCPrepSQLGen::MakeDROPFIELD( XCBuffer* buf, XCField* fld )
{
	INFUNC( MakeDROPFIELD, app );

	Clear();

	type = SQL_STMT_ALTER;
	AlterType = ALTER_DROPFIELD;
	buffer = buf;
	fields = new XCField*[1];
	fields[0] = fld;

	OUTFUNC();
}

void XCPrepSQLGen::MakeADDCON( XCBuffer* buf, buffer_db_relate* idbr, int count, XCAppInterface* App )
{
	INFUNC( MakeADDCON, app );
	int i;

	Clear();

	type = SQL_STMT_ALTER;
	AlterType = ALTER_ADDCON;
	buffer = buf;
	ConsApp = App;
	dbr = new buffer_db_relate*[count];
	dbrCount = count;
	for( i=0; i<count; i++ )
		dbr[i] = &idbr[i];

	OUTFUNC();
}

void XCPrepSQLGen::MakeDROPCON( XCBuffer* buf, buffer_db_relate *idbr, int count, XCAppInterface* App )
{
	INFUNC( MakeADDCON, app );
	int i;

	Clear();

	type = SQL_STMT_ALTER;
	AlterType = ALTER_DROPCON;
	buffer = buf;
	ConsApp = App;
	dbr = new buffer_db_relate*[count];
	dbrCount = count;
	for( i=0; i<count; i++ )
		dbr[i] = &idbr[i];

	OUTFUNC();
}

void XCPrepSQLGen::CreateAUTONUMBER( XCBuffer* buf )
{
	INFUNC( CreateAUTONUMBER, app );

	type = SQL_STMT_CREATE;
	buffer = buf;
	AutoNum = true;

	OUTFUNC();
}

void XCPrepSQLGen::RemoveAUTONUMBER( XCBuffer* buf )
{
	INFUNC( RemoveAUTONUMBER, app );

	type = SQL_STMT_DROP;
	buffer = buf;
	AutoNum = true;

	OUTFUNC();
}

void XCPrepSQLGen::MakeFINALIZE( XCBuffer* buf, XCField* fld, int mode ) {
	Clear();

	type = SQL_STMT_FINALIZE;
	if (mode > 0) {
		if (fld)
			AlterType = ALTER_ADDFIELD;
		else
			AlterType = ALTER_ADDTABLE;
	} else {
		if (fld)
			AlterType = ALTER_DROPFIELD;
		else
			AlterType = ALTER_DROPTABLE;
	};
	buffer = buf;
	fields = new XCField*[1];
	fields[0] = fld;
};

bool XCPrepSQLGen::MakeRENAME( XCBuffer* buf, char* src, char* tgt, XCAppInterface* app )
{
	INFUNC( XCPrepSQLGen::MakeRENAME, app );
	bool retval = true;

	type = SQL_STMT_RENAME;
	buffer = buf;
	
	// return wether or not the database supports table renaming
	long db_nbr = buf->GetDatabaseInfo()->number;
	XCDBInterface* dbi = app->GetDatabase()->GetInterface( db_nbr );
	if(!dbi->GetDatabaseInfo()->supports_table_rename)
		retval = false;

	// if we can rename, copy the source and target
	if( retval ) {
		source = new char[ strlen( src )+1 ];
		strcpy( source, src );
		target = new char[ strlen( tgt )+1 ];
		strcpy( target, tgt );
	}

	OUTFUNCRET( retval );
}


//////
//
// XCSQLStatement class - prep functions
//
//////

void XCSQLStatement::MakeDROP()
{
	INFUNC( XCSQLStatement::MakeDROP, pquery->GetApp() );
	char *retval = sql_data;
	XCBuffer* buf = query->GetBuffer();
	
	/////////////////////
	// DROP TABLE <table>
	/////////////////////

	if( pquery->AutoNum )
		RemoveAUTONUMBER();
	else
		retval += sprintf( retval, "DROP TABLE %s", buf->GetFormatName() );

	OUTFUNC();
}



int XCSQLStatement::GetDefault( XCField* fld, char* data ) {
	int result = 0;

	switch( fld->GetType() ) {
		case DEC_FLOAT:
		case DEC_DOUBLE:
			// DECIMAL TYPES
			result = sprintf( data, " DEFAULT %f", *((double*)fld->GetOffset()) );
			break;

		case DATE_TIMESTAMP:
		case TIME_24HR_DB:
		case DATE_DB:
		case DUR_HM_TIME_DISP:
		case DUR_HH_TIME_DISP:
			// Date and interval types
			result = sprintf( data, " DEFAULT %s", FixString( fld ));
			break;

		case S_INTEGER:
			result = sprintf( data, " DEFAULT %hd", *((short int*)fld->GetOffset()) );
			break;

		case LITERAL_VAL:
		case D_INTEGER:
			result = sprintf( data, " DEFAULT %ld", *((long*)fld->GetOffset()) );
			break;

		case XCBOOL:
			result = sprintf( data, " DEFAULT '%d'", fld->IsTrue() ? 1 : 0 );
			break;

		case AUTONUMBER:
		case XCBLOB:
			// IGNORE - NO DEFAULTS FOR NOW
			break;

		case DATE_JULIAN:
		case DECIMAL_64:
			// WEIRD TYPES - this seems wrong
			result = sprintf( data, " DEFAULT %s", fld->GetOffset() );
			break;

		default:	// String types
			result = sprintf( data, " DEFAULT '%s'", fld->GetOffset() );
	};
	return result;
};



int XCSQLStatement::GetFullDataType( XCField* fld, char* start, bool use_defaults ) {
	bool do_null = true;
	char* retval = start;

	// First get the name of the field
	retval += GetDataType( fld, retval, do_null );

	// Next, tack on the default value
	if (use_defaults)
		retval += GetDefault( fld, retval );

	// Last, tack on NOT NULL check
	if ( do_null & !( fld->GetFlags() & FLAG_ALLOW_NULL) && fld->GetType() != XCBLOB && fld->GetType() != AUTONUMBER)
		retval += sprintf( retval, " NOT NULL" );

	return (retval - start);
};



int XCSQLStatement::GetDataType( XCField* fld, char* start, bool& do_not_null )
{
	INFUNC( XCSQLStatement::GetDataType, pquery->app );
	char* data = start;
	int type = fld->GetType();

	switch(type) {
		// String types
		case BIG_STRING:
		case STRING:
		case ALPHA_STRING:

		// Interval types
		case DUR_HM_TIME_DISP:
		case DUR_HH_TIME_DISP:
			data += sprintf( data, "CHAR(%ld)", fld->GetLength() );
			break;
	
		case VAR_STRING:
			data += sprintf( data, "VARCHAR(%ld)", fld->GetLength() );
			break;

		case DEC_FLOAT:
		case DEC_DOUBLE:
			data += sprintf( data, "DOUBLE PRECISION" );
			break;

		case DATE_JULIAN:
		case DECIMAL_64:
			data += sprintf( data, "DECIMAL(%ld,%ld)", fld->GetLength(), fld->GetDecPlaces() );
			break;

		case S_INTEGER:
			data += sprintf( data, "SMALLINT" );
			break;

		case LITERAL_VAL:
		case D_INTEGER:
			data += sprintf( data, "INT" );
			break;

		case XCBOOL:
			data += sprintf( data, "CHAR(1)" );
			break;

		case AUTONUMBER:
			data += sprintf( data, "INT" );
			// individual drivers may need to overwrite this one!!
			break;

		case DATE_TIMESTAMP:
			data += sprintf( data, "TIMESTAMP" );
			break;

		case TIME_24HR_DB:
			data += sprintf( data, "TIME" );
			break;

		case DATE_DB:
			data += sprintf( data, "DATE" );
			break;

		case XCBLOB:
			// no general case for a BLOB.  Drivers must overwrite this to support BLOB types
			break;

		default:
			RAISETOOLSERROR_3PARAM( ERR_INV_SQL_DATA_TYPE, type, fld->GetOwner()->GetFormatName(), fld->GetFieldName() );
			break;
	}

	OUTFUNCRET( (data - start ) );
}

int XCSQLStatement::AddCON( char *start )
{
	INFUNC( XCSQLStatement::AddCON, pquery->app );
	char *data = start;
	int i;
	buffer_format* format;
	buffer_field* fld;
	buffer_field* bufinfo;
	buffer_db_relate** dbr = pquery->dbr;
	XCAppInterface* app = pquery->app;
	long buff_number = pquery->buffer->GetFormatNumber();

	data += sprintf( data, "ADD CONSTRAINT T%ld_%s%ld",
		pquery->real_app_num, buff_number < 0 ? "U" : "", abs(buff_number) );

	// handle constraint type
	switch( dbr[0]->type )
	{
	case dbr_primary_key:
		// Primary key constraint name: [App#]_U[abs(Buf#)]
		// U is only added if the buffer number is negative
		data += sprintf( data, "_PK PRIMARY KEY(" );

		// all field in primary key
		for( i=0; i<pquery->dbrCount; i++ ) {
			bufinfo = (buffer_field*)app->FixOffset( dbr[i]->field_offset );
			fld = app->GetFieldInfo( bufinfo->number );
			if (i>0)
				data += sprintf( data, "," );
			data += sprintf( data, "%s", fld->name );
		}

		data += sprintf( data, ")" );
		break;

	case dbr_unique_key:
		// list field numbers in constraint name
		for( i=0; i<pquery->dbrCount; i++ ) {
			bufinfo = (buffer_field*)app->FixOffset( dbr[i]->field_offset );
			data += sprintf( data, "_%s%ld",
				bufinfo->number < 0 ? "U" : "", abs(bufinfo->number) );
		}
		data += sprintf( data, " UNIQUE(" );

		// all field in unique key
		for( i=0; i<pquery->dbrCount; i++ ) {
			bufinfo = (buffer_field*)app->FixOffset( dbr[i]->field_offset );
			fld = app->GetFieldInfo( bufinfo->number );
			if( i>0 ) 
				data += sprintf( data, "," );
			data += sprintf( data, "%s", fld->name );
		}
		data += sprintf( data, ")" );
		break;

	case dbr_foreign_key:
		format = (buffer_format*)app->FixOffset( dbr[0]->format_offset );
		fld = (buffer_field*)app->FixOffset( dbr[0]->field_offset );

		data += sprintf( data, "_%s%ld_FK FOREIGN KEY (", 
			fld->number < 0 ? "U" : "", abs(fld->number) );
		
		// all field in the foreign key
		for( i=0; i<pquery->dbrCount; i++ ) {
			bufinfo = (buffer_field*)app->FixOffset( dbr[i]->field_offset );
			fld = app->GetFieldInfo( bufinfo->number );
			if (i>0)
				data += sprintf( data, "," );
			data += sprintf( data, "%s", fld->name );
		}
		data += sprintf( data, ") REFERENCES %s (", format->name );

		for( i=0; i<pquery->dbrCount; i++ ) {
			if( dbr[i]->field_tgt_offset )
				fld = (buffer_field*)app->FixOffset( dbr[i]->field_tgt_offset );
			else
				fld = (buffer_field*)app->FixOffset( dbr[i]->field_offset );
			if (i>0)
				data += sprintf( data, "," );
			data += sprintf( data, "%s", fld->name );
		}

		data += sprintf( data, ")" );
		break;

	case dbr_index_key:
		fld = (buffer_field*)app->FixOffset( dbr[0]->field_offset );

		data = sql_data;	// start over with the whole statement, kind of a hack, but oh well
		data += sprintf( data, "CREATE INDEX T%ld_%s%ld_%s%ld_INDEX ON %s (", 
			pquery->real_app_num,
			buff_number < 0 ? "U" : "", abs(buff_number),
			fld->number < 0 ? "U" : "", abs(fld->number),
			pquery->buffer->GetFormatName() );	

		// all field in the index key
		pquery->dbrCount = 1;			// not sure we are hardcoding this to only one right now... probably come back to it
		for( i=0; i<pquery->dbrCount; i++ ) {
			bufinfo = (buffer_field*)app->FixOffset( dbr[i]->field_offset );
			fld = app->GetFieldInfo( bufinfo->number );
			if (i>0)
				data += sprintf( data, "," );
			data += sprintf( data, "%s", fld->name );
		}

		data += sprintf( data, ")" );
		break;

	case dbr_default:
		fld = (buffer_field*)app->FixOffset( dbr[0]->field_offset );

		data = start;	// start over with the ALTER TABLE statement, kind of a hack, but oh well
		data += sprintf( data, "ADD" );
		data += GetDefault( pquery->buffer->GetField( fld->number ), data );
		data += sprintf( data, " FOR %s", fld->name );
		break;

	default:
		RAISETOOLSERROR( ERR_INV_SQL_CONS_TYP );
		break;
	}

	OUTFUNCRET( (data - start) );
}

		
int XCSQLStatement::DropCON( char *start )
{
	INFUNC( XCSQLStatement::DropCON, pquery->app );
	char *data = start;
	int i;
	buffer_format* format;
	buffer_field* fld;
	buffer_field* bufinfo;
	buffer_db_relate** dbr = pquery->dbr;
	XCAppInterface* app = pquery->app;

	long buff_number = pquery->buffer->GetFormatNumber();
	data += sprintf( data, "DROP CONSTRAINT T%ld_%s%ld",
		pquery->real_app_num, buff_number < 0 ? "U" : "", abs(buff_number) );

	switch( dbr[0]->type )
	{
	case dbr_primary_key:
		data += sprintf( data, "_PK" );
		break;
		
	case dbr_unique_key:
		for( i=0; i<pquery->dbrCount; i++ ) {
			bufinfo = (buffer_field*)app->FixOffset( dbr[i]->field_offset );
			data += sprintf( data, "_%s%ld",
				bufinfo->number < 0 ? "U" : "", abs(bufinfo->number) );
		}
		break;

	case dbr_foreign_key:
		fld = (buffer_field*)app->FixOffset( dbr[0]->field_offset );
		data += sprintf( data, "_%s%ld_FK",
			fld->number < 0 ? "U" : "", abs(fld->number) );
		break;

	case dbr_index_key:
		fld = (buffer_field*)app->FixOffset( dbr[0]->field_offset );
		data += sprintf( data, "_%s%ld_INDEX",
			fld->number < 0 ? "U" : "", abs(fld->number) );
		break;

	case dbr_default:
		fld = (buffer_field*)app->FixOffset( dbr[0]->field_offset );
		data += sprintf( data, "_%s%ld_DF",
			fld->number < 0 ? "U" : "", abs(fld->number) );
		break;

	default:
		RAISETOOLSERROR( ERR_INV_SQL_CONS_TYP );
		break;
	}

	OUTFUNCRET( (data - start) );
}

void XCSQLStatement::MakeALTER()
{
	INFUNC( XCSQLStatement::MakeALTER, pquery->app );
	char *retval = sql_data;
	XCBuffer* buf = pquery->buffer;
	SQL_STMT_ALTER_type AlterType = pquery->AlterType;
	
	/////////////////////////////////////////////////////////////
	// ALTER TABLE <table> ADD|DROP [CONSTRAINT]  ...
	/////////////////////////////////////////////////////////////

	retval += sprintf( retval, "ALTER TABLE %s ", buf->GetFormatName() );

	switch (AlterType) 
	{
	case ALTER_ADDFIELD:
		retval += sprintf( retval, "ADD %s ", pquery->fields[0]->GetFieldName() );
		retval += GetFullDataType( pquery->fields[0], retval, true );
		break;
	case ALTER_DROPFIELD:
		retval += sprintf( retval, "DROP COLUMN %s", pquery->fields[0]->GetFieldName() );
		break;
	case ALTER_ADDCON:
		retval += AddCON( retval );
		break;
	case ALTER_DROPCON:
		retval += DropCON( retval );
		break;
	default:
		RAISETOOLSERROR(-1);
		break;
	}

	OUTFUNC();
}

void XCSQLStatement::MakeRENAME()
{
	INFUNC( XCSQLStatement::MakeRENAME, pquery->GetApp() );
	char *retval = sql_data;
	retval += sprintf( retval, "RENAME TABLE %s TO %s", pquery->source, pquery->target );
	OUTFUNC();
}

void XCSQLStatement::CreateAUTONUMBER()
{
	Clear();	// so nothing stupid is executed
}

void XCSQLStatement::RemoveAUTONUMBER()
{
	Clear();	// so nothing stupid is executed
}

void XCSQLStatement::MakeCREATE()
{
	INFUNC( XCSQLStatement::MakeCREATE, pquery->GetApp() );
	char *retval = sql_data;
	XCBuffer* buf = pquery->GetBuffer();
	XCField* fld = NULL;
	long fieldcount = buf->GetFieldCount();
	int i;

	if( pquery->AutoNum ) 
		CreateAUTONUMBER();
	else {

	////////////////////////////////////////////////////////////////
	// CREATE TABLE <table> ( <field> <datatype> [ (<size>) ], ... )
	////////////////////////////////////////////////////////////////

	// CREATE TABLE <table> (
	retval += sprintf( retval, "CREATE TABLE %s (", buf->GetFormatName() );

	// Determine if we should use defaults in the create statement
	bool use_defaults = true;
	XCDBDriverDescr* desc = buf->APPDIC->GetDBDriverDesc( buf );
	use_defaults = !desc->uses_default_constraint;

	// [,]<field> <datatype> [(<size>)]
	for( i=0; i<fieldcount; i++ )
	{
		fld = buf->GetFieldByIndex(i);
		if( i>0 ) 
			retval += sprintf( retval, "," );
		retval += sprintf( retval, "%s ", fld->GetFieldName() );

		retval += GetFullDataType( fld, retval, use_defaults );
	}
	retval += sprintf( retval, ")" );

	}	// AutoNum if

	OUTFUNC();
}

